#!/use/bin/python
# coding=utf-8
# 核心指标-经验值算法-作息时间准确率
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql):
    conn, cur = getDataConnection()
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num
    return countNum

#计算百分比 保留两位小数  如:34.88%
# X为分子 Y为分母
def getRateByXY(X,Y):
    rate = 0
    if Y != 0:
        if X > Y:
            rate = 100
        else:
            rate = round(X / Y, 4) * 100
    return rate

# 记录 核心指标-经验值算法-作息时间准确率 analysis_experience_time_rate --起床时间
def insertWakeup(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_experience_time_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) num from analysis_experience_time_rate t where 1=1 and t.ref_date =  '{date}' and  type ='wakeup' ")
        # 获取当日 生活助手所有设备量
        deviceNum = selectNumBySql(f"select count(1) from proactive_service_device t ")
        # 获取当日 预测起床时间不为空的数量
        predictionNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.prediction_wakeup_time is not null ")
        # 获取当日 进行计算准确率的数量(预测时间和用户设置的时间同时存在的数量)
        calculateNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.prediction_wakeup_time is not null and t.wakeup_time is not null")
        # 获取预测时间段完全一致的数量
        equalNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.prediction_wakeup_time is not null and t.wakeup_time is not null "\
            f"and t.prediction_wakeup_time = t.wakeup_time")
        #计算 预测时间段10分钟内数量
        selectAccuracyNumSql = """
            select count(1) from (
select t.wakeup_time,t.prediction_wakeup_time,concat('2022-07-28 ',wakeup_time) wakeup_time_d, concat('2022-07-28 ',prediction_wakeup_time),
unix_timestamp(STR_TO_DATE(concat('2022-07-28 ',wakeup_time),'%Y-%m-%d %h:%i:%s')) prediction_wakeup_time_d,
unix_timestamp(STR_TO_DATE(concat('2022-07-28 ',wakeup_time),'%Y-%m-%d %h:%i:%s')) wakeup_time_t,
unix_timestamp(STR_TO_DATE(concat('2022-07-28 ',prediction_wakeup_time),'%Y-%m-%d %h:%i:%s')) prediction_wakeup_time_t
from proactive_service_device t where t.prediction_wakeup_time is not null and t.wakeup_time is not null
and t.prediction_wakeup_time != t.wakeup_time) tt where (wakeup_time_t - prediction_wakeup_time_t) BETWEEN -600 and 600
                """
        accuracyNum = selectNumBySql(selectAccuracyNumSql)
        accuracyNum = accuracyNum + equalNum
        # 计算 预测时间覆盖率、 作息时间准确度
        predictionRate = getRateByXY(predictionNum,deviceNum)
        equalRate = getRateByXY(equalNum,calculateNum)
        accuracyRate = getRateByXY(accuracyNum,calculateNum)

        if countNum == 0:
            logDetail = f"{date}日新增-起床时间算法准确度,设备总量为{deviceNum},有预测时间数为{predictionNum},预测时间覆盖率为{predictionRate},有预测及实际时间数{calculateNum},完全一致数{equalNum},一致率{equalRate},误差10分钟内{accuracyNum},准确率{accuracyRate}"
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_experience_time_rate(device_num,prediction_num,prediction_rate,calculate_num,equal_num,equal_rate,accuracy_num,accuracy_rate,type,ref_date) 
                value(%s,%s,%s,%s,%s,%s,%s,%s,'wakeup','%s')""" % (deviceNum,predictionNum,predictionRate,calculateNum,equalNum,equalRate,accuracyNum,accuracyRate,date)
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-起床时间算法准确度,设备总量为{deviceNum},有预测时间数为{predictionNum},预测时间覆盖率为{predictionRate},有预测及实际时间数{calculateNum},完全一致数{equalNum},一致率{equalRate},误差10分钟内{accuracyNum},准确率{accuracyRate}"
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_experience_time_rate set device_num = %s, prediction_num = %s , prediction_rate =%s ,calculate_num = %s,equal_num = %s,equal_rate = %s ,accuracy_num = %s,accuracy_rate = %s ,update_time = now()  where
                    ref_date = '%s' and type = 'wakeup'
            """ % (deviceNum,predictionNum,predictionRate,calculateNum,equalNum,equalRate,accuracyNum,accuracyRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 核心指标-经验值算法-作息时间准确率 analysis_experience_time_rate --睡觉时间
def insertSleep(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_experience_time_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) num from analysis_experience_time_rate t where 1=1 and t.ref_date =  '{date}' and  type ='sleep' ")
        # 获取当日 生活助手所有设备量
        deviceNum = selectNumBySql(f"select count(1) from proactive_service_device t  ")
        # 获取当日 预测睡觉时间不为空的数量
        predictionNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.prediction_sleep_time is not null ")
        # 获取当日 进行计算准确率的数量(预测时间和用户设置的时间同时存在的数量)
        calculateNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.prediction_sleep_time is not null and t.sleep_time is not null")
        # 获取预测时间段完全一致的数量
        equalNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.prediction_sleep_time is not null and t.sleep_time is not null " \
            f"and t.prediction_sleep_time = t.sleep_time")
        #计算 预测时间段10分钟内数量
        selectAccuracyNumSql = """
            select count(1) from (
select t.sleep_time,t.prediction_sleep_time,concat('2022-07-28 ',sleep_time) sleep_time_d, concat('2022-07-28 ',prediction_sleep_time),
unix_timestamp(STR_TO_DATE(concat('2022-07-28 ',sleep_time),'%Y-%m-%d %h:%i:%s')) prediction_sleep_time_d,
unix_timestamp(STR_TO_DATE(concat('2022-07-28 ',sleep_time),'%Y-%m-%d %h:%i:%s')) sleep_time_t,
unix_timestamp(STR_TO_DATE(concat('2022-07-28 ',prediction_sleep_time),'%Y-%m-%d %h:%i:%s')) prediction_sleep_time_t
from proactive_service_device t where t.prediction_sleep_time is not null and t.sleep_time is not null
and t.prediction_sleep_time != t.sleep_time) tt where (sleep_time_t - prediction_sleep_time_t) BETWEEN -600 and 600
                """
        accuracyNum = selectNumBySql(selectAccuracyNumSql)
        accuracyNum = accuracyNum + equalNum
        # 计算 预测时间覆盖率、 作息时间准确度
        predictionRate = getRateByXY(predictionNum, deviceNum)
        equalRate = getRateByXY(equalNum, calculateNum)
        accuracyRate = getRateByXY(accuracyNum, calculateNum)

        if countNum == 0:
            logDetail = f"{date}日新增-睡觉时间算法准确度,设备总量为{deviceNum},有预测时间数为{predictionNum},预测时间覆盖率为{predictionRate},有预测及实际时间数{calculateNum},完全一致数{equalNum},一致率{equalRate},误差10分钟内{accuracyNum},准确率{accuracyRate}"
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_experience_time_rate(device_num,prediction_num,prediction_rate,calculate_num,equal_num,equal_rate,accuracy_num,accuracy_rate,type,ref_date) 
                value(%s,%s,%s,%s,%s,%s,%s,%s,'sleep','%s')""" % (
            deviceNum, predictionNum, predictionRate, calculateNum, equalNum, equalRate, accuracyNum, accuracyRate,
            date)
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-睡觉时间算法准确度,设备总量为{deviceNum},有预测时间数为{predictionNum},预测时间覆盖率为{predictionRate},有预测及实际时间数{calculateNum},完全一致数{equalNum},一致率{equalRate},误差10分钟内{accuracyNum},准确率{accuracyRate}"
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_experience_time_rate set device_num = %s, prediction_num = %s , prediction_rate =%s ,calculate_num = %s,equal_num = %s,equal_rate = %s ,accuracy_num = %s,accuracy_rate = %s ,update_time = now()  where
                    ref_date = '%s' and type = 'sleep'
            """ % (
            deviceNum, predictionNum, predictionRate, calculateNum, equalNum, equalRate, accuracyNum, accuracyRate,
            date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 核心指标-经验值算法-作息时间准确率 analysis_experience_time_rate --移动mac
def insertMobileMac(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_experience_time_rate 表是否已经存在当天 数据
        countNum = selectNumBySql(f"select count(1) num from analysis_experience_time_rate t where 1=1 and t.ref_date =  '{date}' and  type ='mobileMac' ")
        # 获取当日 生活助手所有设备量
        deviceNum = selectNumBySql(f"select count(1) from proactive_service_device t  ")
        # 获取当日 预测移动mac不为空的数量
        predictionNum = selectNumBySql(f"select count(1) from proactive_service_device t where t.mobile_mac is not null ")
        # 计算 预测时间覆盖率、 作息时间准确度
        predictionRate = getRateByXY(predictionNum, deviceNum)

        if countNum == 0:
            logDetail = f"{date}日新增-移动mac算法准确度,设备总量为{deviceNum},有预测时间数为{predictionNum},预测时间覆盖率为{predictionRate}"
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_experience_time_rate(device_num,prediction_num,prediction_rate,type,ref_date) 
                value(%s,%s,%s,'mobileMac','%s')""" % (
            deviceNum, predictionNum, predictionRate, date)
            cur.execute(insertSql)
        else:
            logDetail = f"{date}日更新-移动mac算法准确度,设备总量为{deviceNum},有预测时间数为{predictionNum},预测时间覆盖率为{predictionRate}"
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_experience_time_rate set device_num = %s, prediction_num = %s , prediction_rate =%s ,update_time = now()  where
                    ref_date = '%s' and type = 'mobileMac'
            """ % (
            deviceNum, predictionNum, predictionRate, date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 55
        jobName = '核心指标-经验值算法-作息时间准确率'
        tableName = 'analysis_experience_time_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    monthdate = getMonthdate(d)
    print ("%s 日期,更新<数据分析-经验值算法-作息时间准确率>报表" % date)
    insertWakeup(date)
    insertSleep(date)
    insertMobileMac(date)

